﻿-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
--	-- Add the parameters for the stored procedure here
--	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
--	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
--AS
--BEGIN
--	-- SET NOCOUNT ON added to prevent extra result sets from
--	-- interfering with SELECT statements.
--	SET NOCOUNT ON;

--    -- Insert statements for procedure here
--	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
--END
--GO

----------------------BEGIN SELECT---------------------------
--proc Select * KhachHang
USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_SelectALLKhachHang]    Script Date: 05/12/2011 03:04:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SelectALLKhachHang]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SelectALLKhachHang]
GO
create proc sp_SelectALLKhachHang
AS
Begin
	SELECT [maKhachHang]
      ,[hoKhachHang]
      ,[tenLot]
      ,[tenKhachHang]
      ,[maLoaiKH]
      ,[diaChi]
      ,[CMND]
      ,[danhDau]
  FROM [KhachHang] 
 End
 Go
 
 --select Thong Tin Khach hang Gộp tên họ
 USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_SelectThongTinKhachHang]    Script Date: 05/12/2011 03:11:07 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SelectThongTinKhachHang]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SelectThongTinKhachHang]
GO

CREATE proc [dbo].[sp_SelectThongTinKhachHang]
AS
Begin
	SELECT [maKhachHang] as [Mã KH]
      ,[hoKhachHang] + [tenLot] + tenKhachHang as [Họ Tên KH]      
      ,l.tenLoai as [Loại KH]
      ,[diaChi] as [Địa chỉ]
      ,[CMND]
  FROM [KhachHang] kh, LoaiKhachHang l
  where l.maLoaiKH = kh.maLoaiKH and kh.danhDau=0
 End
GO
 
 --
 --proc thong tin KhachHang danhDau = 0 
 USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_SelectThongTinKhachHangChuaDanhDau]    Script Date: 05/12/2011 03:03:14 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SelectThongTinKhachHangChuaDanhDau]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SelectThongTinKhachHangChuaDanhDau]
GO
create proc sp_SelectThongTinKhachHangChuaDanhDau
AS
Begin
	SELECT [maKhachHang] as [Mã Khách Hàng]
      ,[hoKhachHang] as [Họ]
      ,[tenLot] as [Tên đệm]
      ,[tenKhachHang] as [Tên]
      ,[diaChi] as [Địa chỉ]
      ,[CMND] 
      ,l.tenLoai as [Loại Khách Hàng]
  FROM [KhachHang] kh, LoaiKhachHang l
  WHERE l.maLoaiKH=kh.maLoaiKH and kh.danhDau=0
 End
 Go
 --exec sp_SelectThongTinKhachHangChuaDanhDau
 
 --select thong tin khach hang theo ten, chua dánh dấu
 USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_SelectKhachHangTheoTen]    Script Date: 05/12/2011 03:06:14 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SelectKhachHangTheoTen]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SelectKhachHangTheoTen]
GO
 create proc sp_SelectKhachHangTheoTen
 @tenKH nvarchar(50)
 AS
 Begin	
	SELECT [maKhachHang] as [Mã]
      ,[hoKhachHang] +' ' +[tenLot] +' '+[tenKhachHang] as [Khách Hàng]
      ,l.tenLoai as [Loại Khách]      
      ,[CMND]    
      ,[diaChi] as [Địa chỉ]  
  FROM [KhachHang] kh, LoaiKhachHang l
  WHERE l.maLoaiKH=kh.maLoaiKH and kh.danhDau=0 and kh.tenKhachHang like  @tenKH +'%'
 End
 Go
--declare @tenKH nvarchar(50)
-- set @tenKH =N'Trầ'
--exec sp_SelectKhachHangTheoTen @tenKH

--select khách hàng có mã cao nhất(mới thêm vào)
USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_SelectKhachHangMoiThem]    Script Date: 05/12/2011 03:05:29 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SelectKhachHangMoiThem]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SelectKhachHangMoiThem]
GO
 create proc sp_SelectKhachHangMoiThem
 AS
 Begin	
	SELECT [maKhachHang] as [Mã]
      ,[hoKhachHang] +' ' +[tenLot] +' '+[tenKhachHang] as [Khách Hàng]
      ,l.tenLoai as [Loại Khách]      
      ,[CMND]    
      ,[diaChi] as [Địa chỉ]  
  FROM [KhachHang] kh, LoaiKhachHang l
  WHERE l.maLoaiKH=kh.maLoaiKH and kh.danhDau=0 
		and maKhachHang = (select MAX(maKhachHang)
							from KhachHang
							)
 End
 Go
--exec sp_SelectKhachHangMoiThem
 
--Proc select * phong
USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_SelectALLPhong]    Script Date: 05/12/2011 03:07:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SelectALLPhong]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SelectALLPhong]
GO
create proc sp_SelectALLPhong
AS
Begin
	SELECT [maPhong]
      ,[tenPhong]
      ,[maLoaiPhong]
      ,[tinhTrang]
      ,[ghiChu]
      ,[danhDau]
  FROM [Phong]
End
Go
--select Thong tin phong danhDau =0
USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_SelectPhongChuaDanhDau]    Script Date: 05/12/2011 02:58:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SelectPhongChuaDanhDau]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SelectPhongChuaDanhDau]
GO
create proc sp_SelectPhongChuaDanhDau
AS
Begin
	SELECT [maPhong] as [Mã Phòng]
      ,[tenPhong] as [Tên Phòng]
      ,l.tenLoaiPhong as[Loại Phòng]
      ,l.donGia as [Đơn Giá]
      ,[ghiChu] as [Ghi Chú]
      --,[tinhTrang] as [Tình Trạng]
  FROM [Phong] p, LoaiPhong l
  WHERE p.maLoaiPhong =l.maLoaiPhong and p.danhDau=0
End
Go
--exec sp_SelectPhongChuaDanhDau

--proc select phong tinh trang trống va chua đánh dấu
USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_SelectPhongTrong]    Script Date: 05/12/2011 03:00:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SelectPhongTrong]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SelectPhongTrong]
GO
create proc sp_SelectPhongTrong
AS
Begin
	SELECT [maPhong] as [Mã Phòng]
      ,[tenPhong] as [Tên Phòng]
      ,l.tenLoaiPhong as[Loại Phòng]
      ,l.donGia as [Đơn Giá]
      ,[ghiChu] as [Ghi Chú]
      --,[tinhTrang] as [Tình Trạng]
  FROM [Phong] p, LoaiPhong l
  WHERE p.maLoaiPhong =l.maLoaiPhong and p.tinhTrang=0 and p.danhDau =0
End
Go
--exec sp_SelectPhongTrong

--proc select phong tinh trang Đã thuê
USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_SelectPhongKhongTrong]    Script Date: 05/12/2011 02:59:43 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SelectPhongKhongTrong]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SelectPhongKhongTrong]
GO
create proc sp_SelectPhongKhongTrong
AS
Begin
	SELECT [maPhong] as [Mã Phòng]
      ,[tenPhong] as [Tên Phòng]
      ,l.tenLoaiPhong as[Loại Phòng]
      ,l.donGia as [Đơn Giá]
      ,[ghiChu] as [Ghi Chú]
      --,[tinhTrang] as [Tình Trạng]
  FROM [Phong] p, LoaiPhong l
  WHERE p.maLoaiPhong =l.maLoaiPhong and p.tinhTrang=1 and p.danhDau=0
End
Go

--exec sp_SelectPhongKhongTrong

--proc select Phong đã thuê và chưa trả có ten phòng = @Ten
USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_SelectPhongDaThueChuaTra]    Script Date: 05/12/2011 02:57:53 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SelectPhongDaThueChuaTra]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SelectPhongDaThueChuaTra]
GO
create proc sp_SelectPhongDaThueChuaTra
	@tenPhong nvarchar(50)
AS
begin
	SELECT * 
	FROM Phong p ,PhieuThuePhong pt --, ChiTietHoaDon ctHD
	WHERE p.maPhong=pt.maPhong and p.tenPhong like @tenPhong + '%' 
		and  pt.maPhieuThue not in (select ctHD.maPhieuThue from ChiTietHoaDon ctHD)
end
Go
--exec sp_SelectPhongDaThueChuaTra N'3'

--kiem tra dang nhap
USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_KiemTraLogin]    Script Date: 05/12/2011 02:56:29 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_KiemTraLogin]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_KiemTraLogin]
GO
create proc sp_KiemTraLogin
	@tenDangNhap nvarchar(50),@matKhau nvarchar(50)
AS
BEGIN
	SELECT [tenDangNhap]
      ,[matKhau]
      ,[hoNguoiDung]
      ,[tenLot]
      ,[tenNguoiDung]
      ,[loaiNguoiDung]
      ,[CMND]
      ,[diaChi]
	  FROM [NguoiDung] nd
	  WHERE nd.tenDangNhap =@tenDangNhap and nd.matKhau = @matKhau
END
Go
--exec sp_KiemTraLogin 'nghiep','e10adc3949ba59abbe56e057f20f883e'

--Danh sách loại phòng danhDau =0
USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_SelectLoaiPhongChuaDanhDau]    Script Date: 05/12/2011 03:08:25 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SelectLoaiPhongChuaDanhDau]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SelectLoaiPhongChuaDanhDau]
GO
create proc sp_SelectLoaiPhongChuaDanhDau
AS
Begin
	SELECT [maLoaiPhong] as [Mã Loại]
      ,[tenLoaiPhong] as [Tên Loại]
      ,[donGia] as[Đơn Giá]
     -- ,[danhDau]
	FROM [LoaiPhong]
	WHERE danhDau = 0
End
Go

--proc tinh tong doanh thu thang
USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_TongDoanhThuThang]    Script Date: 05/24/2011 10:31:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_TongDoanhThuThang]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_TongDoanhThuThang]
GO

USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_TongDoanhThuThang]    Script Date: 05/24/2011 10:31:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create proc [dbo].[sp_TongDoanhThuThang]
	@thang int, @nam int, @tongDT float out
AS
Begin
	  set @tongDT =( SELECT COALESCE(SUM(tongTien),0)
					  FROM HoaDon
					  WHERE  DATEPART(MONTH,ngayLapHoaDon) =@thang 
						and DATEPART(YEAR,ngayLapHoaDon)= @nam 
					)
End

GO

--declare @tong float
--exec sp_TongDoanhThuThang 1,2011,@tong out
--print @tong

--proc tong doanh thu thang theo loai phong
/****** Object:  StoredProcedure [dbo].[sp_TongDoanhThuThangTheoLoaiPhong]    Script Date: 05/24/2011 10:31:19 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_TongDoanhThuThangTheoLoaiPhong]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_TongDoanhThuThangTheoLoaiPhong]
GO

USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_TongDoanhThuThangTheoLoaiPhong]    Script Date: 05/24/2011 10:31:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create proc [dbo].[sp_TongDoanhThuThangTheoLoaiPhong]
	@thang int, @nam int, @loaiPhong int, @tongDT float out
As
Begin
	set @tongDT =( SELECT COALESCE(SUM(ct.soNgayThue*ct.donGia),0)
					  FROM ChiTietHoaDon ct, HoaDon hd,PhieuThuePhong pt, Phong p
					  WHERE  DATEPART(MONTH,hd.ngayLapHoaDon) =@thang 
						and DATEPART(YEAR,hd.ngayLapHoaDon)= @nam 
						and ct.maHoaDon = hd.maHoaDon
						and ct.maPhieuThue = pt.maPhieuThue
						and pt.maPhong = p.maPhong
						and p.maLoaiPhong = @loaiPhong
					)		
End

GO

--Bao cao doanh thu theo LoaiPhong
--create proc sp_BaoCaoDoanhThu
--AS
--Begin
--	declare @loaiPhong int
--	declare @cur cursor
--	set @cur = (select maLoaiPhong from LoaiPhong)
--	open @cur
--	fetch next from @cur into @loaiPhong
--	while @@FETCH_STATUS =0
--	begin
		
--	end
--End
--Go
--declare @mydate datetime
--set @mydate = '2011-04-08 00:00:00.000'
--SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101)
--SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101)

-- tinh so ngay thue cua 1 phieu thue trong thang
create proc sp_SoNgayThueCuaPhieuThueTrongThang
	@thang int, @nam int, @maPhieuThue int, @soNgay int out
AS
Begin
	declare @ngayThue datetime 
	set @ngayThue =(select ngayThue 
							from PhieuThuePhong pt 
							where pt.maPhieuThue=@maPhieuThue)
	if @thang = (select datepart(month,(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@ngayThue))),DATEADD(mm,1,@ngayThue)),101)))) 
			and @nam = (select datepart(YEAR,(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@ngayThue))),DATEADD(mm,1,@ngayThue)),101))))
	--neu thang cua ngay thue = thang dang xet	
	Begin					
		declare @ngayCuoiThang datetime
		set @ngayCuoiThang =( SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@ngayThue))),DATEADD(mm,1,@ngayThue)),101))
								
		if @maPhieuThue in( select pt.maPhieuThue
								from PhieuThuePhong pt, HoaDon hd, ChiTietHoaDon ct 
								where pt.maPhieuThue=ct.maPhieuThue and hd.maHoaDon =ct.maHoaDon) --neu phieu thue da tra
		begin--neu phieu thue co ngay tra
			declare @ngayTra datetime
			set @ngayTra = (select hd.ngayLapHoaDon 
								from HoaDon hd, ChiTietHoaDon ct 
								where hd.maHoaDon =ct.maHoaDon and ct.maPhieuThue= @maPhieuThue)--ngay tra
			
			if @thang = (select datepart(month,(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@ngayTra))),DATEADD(mm,1,@ngayTra)),101)))) 
				and @nam = (select datepart(YEAR,(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@ngayTra))),DATEADD(mm,1,@ngayTra)),101))))
				--neu ngay tra nam trong thang
			begin
				set @soNgay= DATEDIFF(d,@ngayThue,@ngayTra)+1
			end
			else --neu ngay tra nam sang thang sau thi lay ngay cuoi thang tru ngay tra
			begin
				
				set @soNgay= DATEDIFF(d,@ngayThue,@ngayCuoiThang) +1
			end
		end
		else--neu phieu thue chua co ngay tra
		begin
			set @soNgay= DATEDIFF(d,@ngayThue,@ngayCuoiThang)+1
		end
	End
	
	else-- neu ngay thue khong nam trong thang dang xet
	begin
		set @soNgay=0
	end
	return @soNgay
End
Go

--declare @soNgay int
--exec sp_SoNgayThueCuaPhieuThueTrongThang 5,2011, 1, @soNgay out
--print @soNgay



--Tinh so ngay thue cua 1 phong trong 1 thang
create proc sp_SoNgayThueCuaPhongTrongThang
@thang int, @nam int, @maPhong int, @soNgay int out
AS
Begin
	set @soNgay=0
	declare @maPhieuThue int
	declare @cur cursor
	set @cur = cursor for select pt.maPhieuThue 
					from PhieuThuePhong pt 
					where pt.maPhong=@maPhong
	open @cur
	fetch next from @cur into @maPhieuThue
	while @@FETCH_STATUS =0
	begin
		declare @soNgayThue int
		exec sp_SoNgayThueCuaPhieuThueTrongThang @thang,@nam, @maPhieuThue, @soNgayThue out
		set @soNgay += @soNgayThue
		
		fetch next from @cur into @maPhieuThue
	end
	
	close @cur
	deallocate @cur
	
	return @soNgay
End
Go

--declare @soNgay int
--exec sp_SoNgayThueCuaPhongTrongThang 5,2011,3,@soNgay out
--print @soNgay


--create proc sp_TyLeSuDungPhongTrongThang
-- @thang int, @nam int,@maPhong int
-- AS
-- Begin
--	declare @soNgay int
--	exec sp_SoNgayThueCuaPhongTrongThang @thang,@nam,@maPhong,@soNgay out
	
--	SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,''))),DATEADD(mm,1,'')),101)
	
-- End
-- GO
------------------------------END SELECT---------------

-------------------------------BEGIN INSERT-------------------

--proc insert Khách Hàng
USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_InsertKhachHang]    Script Date: 05/12/2011 16:30:49 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_InsertKhachHang]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_InsertKhachHang]
GO
create proc sp_InsertKhachHang
	@hoKH nvarchar(50)
	,@tenLot nvarchar(50)
	, @tenKH nvarchar(50)
	,@maLoaiKH int
	,@diaChi nvarchar(MAX)
	,@CMND int
AS
Begin
	INSERT INTO [KhachHang]
           ([hoKhachHang]
           ,[tenLot]
           ,[tenKhachHang]
           ,[maLoaiKH]
           ,[diaChi]
           ,[CMND]
           ,[danhDau])
     VALUES(@hoKH
           ,@tenLot
           ,@tenKH
           ,@maLoaiKH
           ,@diaChi
           ,@CMND
           ,0
           )
End
Go
--declare @hoKH nvarchar(50)
--declare @tenLot nvarchar(50)
--declare @tenKH nvarchar(50)
--declare @maLoaiKH int
--declare @diaChi nvarchar(MAX)
--declare @CMND int
--set @hoKH = N'Trịnh'
--set @tenLot = 'Văn'
--set @tenKH  = 'Khoa'
--set @maLoaiKH = 1
--set @diaChi  =N'20 Lý Chính Thắng, Q3'
--set @CMND ='123454321'
--exec sp_InsertKhachHang @hoKH,@tenLot,@tenKH,@maLoaiKH,@diaChi,@CMND

-------------------------------END INSERT---------------------

--------------------------------BEGIN UPDATE-----------------

---update ALL thuoc tinh KhachHang
USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_UpdateKhachHang]    Script Date: 05/12/2011 16:38:03 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_UpdateKhachHang]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_UpdateKhachHang]
GO
create proc sp_UpdateKhachHang
	@maKH int
	,@hoKH nvarchar(50)
	,@tenLot nvarchar(50)
	,@tenKH nvarchar(50)
	,@maLoaiKH int
	,@diaChi nvarchar(MAX)
	,@CMND int
	,@danhDau int
AS
Begin
	update KhachHang 
	set 
		hoKhachHang = @hoKH
		,tenLot = @tenLot
		,tenKhachHang = @tenKH
		,maLoaiKH = @maLoaiKH
		,diaChi = @diaChi
		,CMND = @CMND
		,danhDau = @danhDau
	where maKhachHang = @maKH
End
Go
---xoa khach hang --> update thuoc tinh danhDau =1
USE [Holybee_QuanLyKhachSan_nmpm]
GO

/****** Object:  StoredProcedure [dbo].[sp_DanhDauKhachHang]    Script Date: 05/12/2011 16:46:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DanhDauKhachHang]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DanhDauKhachHang]
GO
create proc sp_DanhDauKhachHang
	@maKH int	
AS
Begin
	update KhachHang 
	set 
		danhDau = 1
	where maKhachHang = @maKH
End
Go


---Update LoaiKhachHang
create proc sp_UpdateLoaiKhachHang
	@maLoai int, @tenLoai nvarchar(50),@heSo float
AS
Begin
	UPDATE [LoaiKhachHang]
   SET [tenLoai] = @tenLoai
      ,[heSoNhan] = @heSo
 WHERE maLoaiKH=@maLoai
End
Go

--declare @maLoai int
--set @maLoai=1
--declare @tenLoai nvarchar(50)
--set @tenLoai=N'Nội Địa'
--declare @heSo float
--set @heSo = 2.3
--exec sp_UpdateLoaiKhachHang @maLoai,@tenLoai,@heSo

--Update NguoiDung
create proc sp_UpdateNguoiDung
	@tenDangNhap nvarchar(50)
	,@matKhau nvarchar(50)
	,@ten nvarchar(50)
	,@ho nvarchar(50)
	,@tenDem nvarchar(50)
	,@cmnd nvarchar(50)
	,@diaChi nvarchar(MAX)
AS
Begin
	UPDATE NguoiDung
   SET [matKhau] = @matKhau
      ,[hoNguoiDung] = @ho
      ,[tenLot] = @tenDem
      ,[tenNguoiDung] = @ten       
      ,[CMND] = @cmnd
      ,[diaChi] = @diaChi
 WHERE tenDangNhap=@tenDangNhap
End
Go
--------------------------------END UPDATE--------------------